databazove

 --------prve cviko--------
 SELECT * FROM sk.student

SELECT * FROM sk.student
 ORDER BY priezvisko

SELECT * FROM sk.student
 ORDER BY 2

SELECT * FROM sk.student
 ORDER BY 2 ASC

SELECT * FROM sk.student
 ORDER BY 2 DESC

SELECT * FROM sk.student
 ORDER BY 1 ASC ,2 DESC

SELECT * FROM sk.student
 ORDER BY 1,priezvisko

SELECT priezvisko FROM sk.student

SELECT priezvisko FROM sk.student
 ORDER BY priezvisko

SELECT priezvisko FROM sk.student
 ORDER BY meno DESC

SELECT priezvisko,meno FROM sk.student
 ORDER BY meno   ------najprv priezvisko az tak meno

SELECT meno||priezvisko FROM sk.student
 ORDER BY meno------spoji meno s priezviskom

SELECT meno||' '||priezvisko FROM sk.student
 ORDER BY meno  -----------spoji a da medzi ne medzeru

SELECT meno||' '||priezvisko AS student FROM sk.student
 ORDER BY student-----AS nazve stlpcek student,nemusi sa tam davat to AS

SELECT meno||' '||priezvisko  student FROM sk.student
 ORDER BY student

SELECT priezvisko||','||meno  student FROM sk.student

SELECT meno||' '||SUBSTR(priezvisko,1,1) FROM sk.student----meno a prve pismeno z priezviska
                                                            SUBSTR(priezvisko,pociatocna poloha,kolko znakov)
SELECT SUBSTR(meno,1,1)||'. '||priezvisko FROM sk.student----J. Hrasko

SELECT meno||' '||SUBSTR(priezvisko,1,1)||'.' FROM sk.student--Jan H.

SELECT meno||' '||SUBSTR(priezvisko,1,1)||'.' AS zlocinec FROM sk.student---nazval som stlpec zlocinec

SELECT meno||' '||meno||'ovic '||SUBSTR(priezvisko,1,1)||'.' AS zlocinec FROM sk.student---Jan Janovic H.

SELECT * FROM sk.student
 WHERE meno='Jan'---------vyberie zaznamy,kde meno je Jan

SELECT * FROM sk.student
 WHERE meno<>'Jan'------------kde nie je Jan

SELECT * FROM sk.student
 WHERE meno!='Jan'--------to iste

SELECT * FROM sk.student
 WHERE NOT(meno='Jan')-----------to iste

SELECT * FROM sk.student
 WHERE SUBSTR(priezvisko,1,1)='B'------vypise vsetkych, ktorych priezvisko sa zacina na B)

SELECT * FROM sk.student
 WHERE SUBSTR(priezvisko,LENGTH(priezvisko),1)='a'------vsetkych,ktorych priezvisko konci na a
                                                     ----LENGTH(priezvisko)---dlzka retazca

SELECT * FROM sk.student
 WHERE priezvisko LIKE '%o%'------priezviska,kde v ktorych sa nachadza o.
                                  %-zastupuje retazec,aj prazdny
                                  _-zastupuje jeden znak

SELECT * FROM sk.student
 WHERE priezvisko LIKE '%o%' AND meno<>'Jan'----dve podmienky   



---------------druhe cviko------------------------------------------------------

SELECT COUNT(*) FROM SK.STUDENT-----POCET ZAZNAMOV

SELECT COUNT(MENO) FROM SK.STUDENT---TO ISTE

SELECT COUNT(MENO) FROM SK.STUDENT WHERE meno='Jan'-------pocet janov

SELECT DISTINCT(meno) FROM SK.STUDENT------vypise vsetky rozne mena, ktore sa nachadzaju..

SELECT COUNT(DISTINCT meno) FROM SK.STUDENT------pocet roznych mien

--vytvorenie tabulky---
CREATE TABLE MB.STUDENT
(
 meno VARCHAR(10),
 priezvisko VARCHAR(20)
)
------ 

---naplnenie tabulky-----
INSERT INTO MB.STUDENT
 VALUES
 ('Jan', 'Hrasko'), 
 ('Ruzena', 'Sipova'), 
 ('Ali', 'Baba'), 
 ('Jan', 'Polienko'), 
 ('Juraj', 'Trulo')  
-------

---zmena zaznamu sipova na lukova---
UPDATE MB.STUDENT
 SET priezvisko='Lukova'
 WHERE priezvisko='Sipova'
------

---vymazanie zaznamu---

DELETE FROM MB.STUDENT
 WHERE priezvisko='Lukova'
-------

DROP TABLE MB.STUDENT---vymazanie tabulky

-----vytvarame novu tabulku---
CREATE TABLE MB.STUDENT
 (id  integer,
  meno VARCHAR(10),
  priezvisko VARCHAR(20),
  datum_nar DATE,
  mesto VARCHAR(20)
 )
--------

---hodnoty novej tabulky----
INSERT INTO mb.student
 Values
 (1,'Jan','Hrasko','5.10.1989','Kosice'),
 (2,'Ruzena','Sipova','2.6.1960','Bardejov'),
 (3,'Ali','Baba','23.2.1901','Baghdad'),
 (4,'Jan','Polienko','24.12.1945','Kosice'),
 (5,'Juraj','Trulo','1.9.1939','KOSICE'),
 (6,'Juraj','Trulo','11.9.2001','Trebisov')  
------

delete from mb.student
 where id=6 

----
-----novy sposob zadavania,prehodene stlpce pri zadavani--
INSERT INTO mb.student(meno,priezvisko,mesto,datum_nar,id)
 VALUES 
 ('Juraj','Trulo','Trebisov','11.9.2001',6)  
------

SELECT COUNT(*) FROM mb.student
 WHERE mesto='Kosice'

------dve nove tabulky---------
CREATE TABLE MB.STUDENT
 (id  integer,
  meno VARCHAR(10),
  priezvisko VARCHAR(20),
  datum_nar DATE,
  id_mesto INTEGER 
 )
 ;
 CREATE TABLE MB.MESTO
 (id integer,
  nazov VARCHAR(20)
)
------------------------

------------naplnenie tych dvoch tabuliek

INSERT INTO mb.student
 Values
 (1,'Jan','Hrasko','5.10.1989',1),
 (2,'Ruzena','Sipova','2.6.1960',2),
 (3,'Ali','Baba','23.2.1901',3),
 (4,'Jan','Polienko','24.12.1945',1),
 (5,'Juraj','Trulo','1.9.1939',1),
 (6,'Juraj','Trulo','11.9.2001',4)
 ;
 INSERT INTO mb.mesto
 Values
 (1,'Kosice'),
 (2,'Bardejov'),
 (3,'Baghdad'),
 (4,'Trebisov')
--------------------    


----------------3. cviko-------------------

select * from mb.mesto, mb.student-----vypis dvoch tabuliek,ale pokombinoval vsetky.je tam 24 zaznamov.

select * from mb.mesto, mb.student
 where mb.student.id=mb.mesto.id-----priradi id ku mestu

select mb.student.id,meno,priezvisko,datum_nar,id_mesto
 from mb.student, mb.mesto
 where mb.student.id_mesto=mb.mesto.id------priradi ku id mesto,ale vypise kratsi vypis

select * from mb.student, mb.mesto
 where mb.student.id_mesto=mb.mesto.id and mb.mesto.nazov='Kosice' ---vypise kosicanov

select count(*) from mb.student, mb.mesto
 where mb.student.id_mesto=mb.mesto.id and mb.mesto.nazov='Kosice' ---pocet ludi z kosic

select m.nazov, count(*) from mb.student s, mb.mesto m
 where s.id_mesto=m.id 
 group by m.nazov---------vypise mesta a pocet ludi z toho mesta.

select m.nazov, count(*) from mb.student s, mb.mesto m
 where s.id_mesto=m.id 
 group by m.nazov
 having count(*)>1-------vypise mesta, ktore maju viac ako 1 obyvatela  v tabulke


select *  from mb.student s
 where '1979-1-1'<s.datum_nar and s.datum_nar<'1990-1-1'------vypise zaznam, ktory je narodeni medzi datumami


select *  from mb.student s
 where s.datum_nar between '1900-1-1' and '1990-1-1'-------to iste

select meno,priezvisko,year(datum_nar) from mb.student s-----year z datumu vytiahne rok

select year(datum_nar),count(*) from mb.student s
 group by s.datum_nar --------------vypise pocet ludi narodenych v rovnakom roku

---------------4 cviko ----------------------
CREATE TABLE MB.STUDENT
 (id  integer,
  meno VARCHAR(10),
  priezvisko VARCHAR(20),
  datum_nar DATE,
  id_mesto INTEGER, 
  primary key(id)
) ------------------primary key s chybou nulovych hodnot,nepripusta prazdne hodnoty

CREATE TABLE MB.STUDENT
 (id  integer Not Null,
  meno VARCHAR(10),
  priezvisko VARCHAR(20),
  datum_nar DATE,
  id_mesto INTEGER, 
  primary key(id)
)----------------- uz bez chyby 

CREATE TABLE MB.MESTO
 (id integer Not null,
  nazov VARCHAR(20),
  primary key(id)
)----------------je dobre ked kazda ma primary key, neumoznuje zadat to iste meno s rovnakym id 

Alter Table mb.student 
ADD Foreign Key sm(id_mesto) references mb.mesto--------------obmedzenie na cudzi kluc,priradi ho stlpcu id_mesto,  odkazuje na tabulku mb.mesto a teraz musime najskor naplnit mesto a potom student, nedovoli pridat id_mesta ktore este nie je v tabulke,kontroluje, aby sa zhodovalo id_mesto v oboch tabulkach, neumozni vymazat mesto na ktore je naviazany student 

Alter Table mb.student
 Add Column izba Char(5)------------ prida do tabulky stlpec izba

UPDATE MB.STUDENT
 SET izba='220B'
 WHERE id=5-----------------doplnime cisla izieb do tab.

Select * from mb.student
 where izba is not null -------------kde niekto byva vypis, Ruzena sa boji byvat na intraku

Select * from mb.student
 where izba is null-------------tych co nebyvaju na intraku-Ruzena

Select * from mb.student
 where Not (izba is null) - to iste ako to prve s not



-------------------------5 cviko----------------------


alter table mb.student drop constraint sm--------odstranili sme sm-to je ten kluc

alter table mb.student add foreign key sm(id_mesto)
 references mb.mesto on delete cascade------------------sm(**)-viaze sa na ** stlpec,tabulky mb.student, odkazujuca na mb.mesto

delete from mb.mesto 
 where nazov='kosice'-------vymaze kosice, ale aj vsetko co na to ukazovalo,cize aj tych, ktory byvaju v kosiciach


select * from mb.student s1,mb.student s2
 where s1.izba=s2.izba and s1.id<s2.id----------poparuje nam tych,ktory byvaju spolu


----------ideme vytvorit nove tabulky,na studenta sa viazu skupina,izba a mesto;na izby sa viaze typ_izby
create table gp.mesto
 (id integer not null,
  nazov varchar(20) not null,
  primary key(id)
 )

create table gp.skupina
 (id integer not null,
 nazov varchar(20) not null,
 kod varchar(5) not null,
 primary key(id)
)

create table gp.typ_izby
 (id integer not null,
 kod varchar(5) not null,
 popis varchar(20) not null,
 kapacita integer not null, 
 poplatok decimal (5,2) not null,
 primary key(id)
)

create table gp.izba
 (id integer not null,
 cislo varchar(5) not null,
 id_typ integer not null,
 primary key(id)
)

create table gp.student
 (id integer not null,
 meno varchar(20) not null,
 priezvisko varchar(20) not null,
 datum_nar date not null,
 pohlavie varchar(1) not null,
 rocnik integer not null,
 id_mesto integer not null,
 id_izba integer,
 id_skupina integer not null,
 primary key(id)
)

---------------------------koniec novych tabuliek

--------------------vezby medzi tabulkami
alter table gp.student add foreign key sm(id_mesto)
 references gp.mesto

alter table gp.student add foreign key ss(id_skupina)
 references gp.skupina

alter table gp.student add foreign key si(id_izba)
 references gp.izba

alter table gp.izba add foreign key it(id_typ)
 references gp.typ_izby

--------------------------------------naplnenie tabuliek

insert into gp.mesto 
 values
 (1,'Kosice'),
 (2,'Bardejov'),
 (3,'Bagdad'),
 (4,'Trebisov')

insert into gp.skupina 
 values
 (1,'Matematika','M'),
 (2,'Informatika','I'),
 (3,'Matematika-Fyzika','MF')

insert into gp.typ_izby
 values 
 (1,'M6A','2 os.bez TV',2,500.00), 
 (2,'M6B','3 bez pristelku',3,450.00) 
 
insert into gp.izba 
 values
 (1,'120A',1),
 (2,'220B',2)

insert into gp.student 
 values
 (1,'Janko','Hrasko','12.12.1988','M',2,1,1,1),
 (2,'Ruzena','Sipova','11.11.1777','F',3,2,Null,3),
 (3,'Ali','Baba','31.7.1981','M',1,3,2,2),
 (4,'Janko','Polienko','24.12.1986','M',2,1,2,3),
 (5,'Juro','Trulo','1.4.1492','M',3,1,2,1),
 (6,'Juro','Trulo','2.4.1492','M',3,4,1,2)
--------------------------------------------------6.cviko

select * from gp.student as a, gp.mesto b, gp.skupina as c, gp.izba as d, gp.typ_izby as e 
 where a.id_mesto=b.id and a.id_izba=d.id and d.id_typ=e.id and a.id_skupina=c.id ---- vypis podla id,ale nebude tam ruzena,lebo izba je null12:22 5.11.2001

Select * from gp.student s
 Join gp.mesto m on s.id_mesto=m.id
 Join gp.skupina g on s.id_skupina=g.id
 Join gp.izba i on s.id_izba=i.id
 Join gp.typ_izby t on i.id_typ=t.id------------ to iste ako predchadzajuce prikazy 

Select * from gp.student s
 Join gp.mesto m on s.id_mesto=m.id
 Join gp.skupina g on s.id_skupina=g.id
 Join gp.izba i on s.id_izba=i.id
 Join gp.typ_izby t on i.id_typ=t.id
 where g.kod='M'---------------- vypise matematikov 


Select * from gp.student s
 Join gp.mesto m on s.id_mesto=m.id
 Join gp.skupina g on s.id_skupina=g.id
 Join gp.izba i on s.id_izba=i.id
 Join gp.typ_izby t on i.id_typ=t.id
 where g.kod='M' or  g.kod='I'------------- matematikov a informatikov

Select * from gp.student s
 Join gp.mesto m on s.id_mesto=m.id
 Join gp.skupina g on s.id_skupina=g.id
 Join gp.izba i on s.id_izba=i.id
 Join gp.typ_izby t on i.id_typ=t.id
 where g.kod in ('M','I') --------------to iste 

Select meno,priezvisko,(case pohlavie when 'M' then 'muz'
				when 'F' then 'zena'
				end)
from gp.student s -----------------vypise namiesto m muz a na miesto f zena
 


Select meno,priezvisko,cislo from gp.student s
 Join gp.izba i on s.id_izba=i.id -----------------meno priezvisko a zktorej izby su

Select meno,priezvisko,cislo from gp.student s
 Left outer Join gp.izba i on s.id_izba=i.id -------------vypis aj z Ruzenou pricom nebyva na intraku,udaj o izbe nie je 
 
 
Select * from gp.student s
 Join gp.mesto m on s.id_mesto=m.id
 Join gp.skupina g on s.id_skupina=g.id
 Left outer Join gp.izba i on s.id_izba=i.id
 Left outer Join gp.typ_izby t on i.id_typ=t.id--------------vypise vsetko aj z Ruzenou 



--------------------------6.cviko----------------
connect to vyuka user vyuka using vyuka 


insert into gp.typ_izby
 values
  (3,'LUX','Apartman',1,999)-----------vlozili sme riadok do typ_izby

insert into gp.izba
 values
 (3,'320A',3)----------------vlozili sme riadok do izba

select  priezvisko,meno,cislo from gp.student s
 left outer join gp.izba i on i.id=s.id_izba--------------aj ruzou


select  priezvisko,meno,cislo from gp.student s
 right outer join gp.izba i on i.id=s.id_izba---------------vypise aj izbu, ktora nie je obsadena

select  priezvisko,meno,cislo from gp.student s
 full outer join gp.izba i on i.id=s.id_izba-----------------vypise izbu aj prazdnu,aj ruzenu


PRIEZVISKO           MENO                 CISLO
-------------------- -------------------- -----
Hrasko               Janko                120A 
Trulo                Juro                 120A 
Baba                 Ali                  220B 
Polienko             Janko                220B 
Trulo                Juro                 220B 
-                    -                    320A 
Sipova               Ruzena               -        ------------------------vypis toho vysie


Select meno,priezvisko,poplatok from gp.student s
 Join gp.izba i on s.id_izba=i.id
 Join gp.typ_izby t on i.id_typ=t.id----------------meno,priezvisko,polpatok,ale len ty co platia


Select meno,priezvisko,poplatok from gp.student s
left outer Join gp.izba i on s.id_izba=i.id
left outer Join gp.typ_izby t on i.id_typ=t.id--------------aj s ruzenou

Select meno,priezvisko,coalesce(poplatok,0) from gp.student s
left outer Join gp.izba i on s.id_izba=i.id
left outer Join gp.typ_izby t on i.id_typ=t.id---------------ak ma poplatok hodnotu null, tak sa vypise 0,plati pre ruzenu


Select popis,sum(poplatok) from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id
 group by popis------------------------poplatok, za ake izby sa kolko plati spolu


Select max(poplatok) from gp.typ_izby---------maximalna cena za izbu

Select min(poplatok) from gp.typ_izby-----------min

Select max(poplatok)-min(poplatok) from gp.typ_izby------------rozdiel najdrahsej a najlacnejsej

Select avg(poplatok) from gp.typ_izby---------priemer



Select priezvisko from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id
 where poplatok=(select min(poplatok) from gp.typ_izby)-----------vypise mena ludi s najnisim poplatkom

Select priezvisko from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id
 where poplatok<=all(select min(poplatok) from gp.typ_izby)---mensi alebo rovny ako minimalny poplatok

Select priezvisko from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id
 where poplatok=(select max(poplatok) from gp.typ_izby---------ten kto plati najdrahsiu izbu,taky nie je

----------------------7 cviko---------------------------

Select priezvisko from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id
 where poplatok>=ALL(select max(poplatok)/2 from gp.typ_izby)
------------------vypise ludi s poplatkom ktory platia viac ako polovicu max poplatku 

Select avg(poplatok) from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id --------------vypise priemernu sumu ktoru plati student

Select * from gp.typ_izby t
 where not t.id in (Select t.id from gp.student s
 join gp.izba i on s.id_izba=i.id
 join gp.typ_izby t on i.id_typ=t.id) ------------vypise typ_izby ktora nie je obsadena

 Select max(poplatok)  from gp.typ_izby
 where poplatok<(select max(poplatok) from gp.typ_izby)--------vypise druhy najvacsi poplatok

Select max(poplatok)  from gp.typ_izby
 where poplatok<(select max(poplatok) from gp.typ_izby
 where poplatok<(select max(poplatok) from gp.typ_izby)) --------treti najvacsi
 
Create table gp.ucitel
 (id integer Not null,
 meno varchar(10) Not null,
 priezvisko varchar(20) Not null,
 Primary key(id)
 )------------naplnenie tab. ucitel

Create tabel gp.predmet
 (id integer Not null,
 kod char(5) Not null,
 nazov Varchar(30) Not null,
 id_ucitel integer Not null,
 kredit integer Not null,
 Primary key(id)
 ) ----------------naplnenie tab. predmet

Create table gp.zapisane
 (id_student integer Not null,
 id_predmet integer Not null,
 znamka char(1),
 Primary key(id_student,id_predmet)
 ) --------------naplnenie tab. zapisane

Alter table gp.predmet add foreign key pu(id_ucitel) references gp.ucitel
Alter table gp.zapisane add foreign key zp(id_predmet) references gp.predmet
Alter table gp.zapisane add foreign key zs(id_student) references gp.student ---- navaznosti na tabulky

Insert into gp.ucitel values
(1,'Dzordz','Kantov'),
(2,'Janamos','Komensky')

Insert into gp.predmet values
(1,'Ma2','Analyza 1',1,2),
(2,'Ma2','Analyza 2',1,3),
(3,'A','Algebra',2,2),
(4,'G','Geometria',2,1)

Insert into gp.zapisane values
(1,1,'A'),
(1,2,'B'),
(1,3,'C'),
(2,2,NULL),
(2,4,'A'),
(3,1,'A'),
(3,3,'D'),
(4,2,NULL),
(5,1,'B'),
(5,2,'C'),
(5,3,NULL),
(6,1,NULL),
(6,2,'D'),
(6,3,'B'),
(6,4,'A'),